package com.scalar.chiptrack.adaptor.dao;

import java.util.Iterator;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import com.scalar.chiptrack.utils.Logger;
import com.scalar.chiptrack.utils.SqlQueryMappings;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.ScalarException;
import com.scalar.chiptrack.adaptor.AdaptorConstants;
import com.scalar.chiptrack.adaptor.AssemblyLot;


public class AssemblyAdaptorDAO implements AdaptorConstants
{
    private static Logger s_logger = null;
    
    public static void saveAssemblyLot( DBTransaction dbTransaction, Iterator assemblyLotIterator, String psType, String vendorName ) throws ScalarException
    {
        saveAssemblyLot( dbTransaction, assemblyLotIterator, psType, vendorName, true );
    }

    public static void saveAssemblyLot( DBTransaction dbTransaction, Iterator assemblyLotIterator, String psType, String vendorName, boolean closeLots ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        boolean success         = false;

        try
        {
            // Get the connection from regular datasource if its not in a manual transaction
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            if ( vendorName != null )
            {
                pstmt = conn.prepareStatement(SqlQueryMappings.get("adaptor.closeAllWipInventoryLots"));
                pstmt.setString(1, psType);
                pstmt.setString(2, vendorName);
                pstmt.executeUpdate();
                pstmt.close();
                pstmt = null;
            }
            else
            {
                pstmt = conn.prepareStatement(SqlQueryMappings.get("adaptor.closeAllWipInventoryLots"));
                pstmt.setString(1, psType);
                pstmt.setString(2, VENDOR_NAME_SCS);
                pstmt.executeUpdate();
                pstmt.close();
                pstmt = null;

                pstmt = conn.prepareStatement(SqlQueryMappings.get("adaptor.closeAllWipInventoryLots"));
                pstmt.setString(1, psType);
                pstmt.setString(2, VENDOR_NAME_SCK);
                pstmt.executeUpdate();
                pstmt.close();
                pstmt = null;

                pstmt = conn.prepareStatement(SqlQueryMappings.get("adaptor.closeAllWipInventoryLots"));
                pstmt.setString(1, psType);
                pstmt.setString(2, VENDOR_NAME_SCC);
                pstmt.executeUpdate();
                pstmt.close();
                pstmt = null;

                pstmt = conn.prepareStatement(SqlQueryMappings.get("adaptor.closeAllWipInventoryLots"));
                pstmt.setString(1, psType);
                pstmt.setString(2, VENDOR_NAME_SCM );
                pstmt.executeUpdate();
                pstmt.close();
                pstmt = null;

            }

            if ( pstmt != null )
            {
                pstmt.close();
                pstmt = null;
            }
            // Step 2
            // Run new values
            pstmt = conn.prepareStatement( SqlQueryMappings.get( "adaptor.saveAssemblyLot" ) );
            AssemblyLot assemblyLot = null;
            log( "Saving Assembly Lot Information... " );
            while( assemblyLotIterator.hasNext() )
            {
                assemblyLot = ( AssemblyLot ) assemblyLotIterator.next();

                if (assemblyLot.getLotNumber() == null) continue;

                log( assemblyLot + "\n" );
                pstmt.setString( 1, assemblyLot.getDeviceName() );
                pstmt.setString( 2, assemblyLot.getLotNumber() );
                pstmt.setString( 3, assemblyLot.getPackageType() );
                pstmt.setString( 4, assemblyLot.getLeadCount() );
                pstmt.setString( 5, assemblyLot.getDateIn() );
                pstmt.setString( 6, assemblyLot.getDateOut() );
                pstmt.setString( 7, assemblyLot.getPONumber());
                pstmt.setString( 8, assemblyLot.getQtyIn() );
                pstmt.setString( 9, assemblyLot.getQtyOut() );
                pstmt.setString( 10, assemblyLot.getExpectedDateOut() );
                pstmt.setString( 11, assemblyLot.getStage() );
                pstmt.setString( 12, assemblyLot.getVendorName() );
                pstmt.setString( 13, assemblyLot.getStatus() );
                pstmt.setString( 14, assemblyLot.getHoldCode() );
                pstmt.setString( 15, assemblyLot.getHoldReason() );
                pstmt.setString( 16, assemblyLot.getFabLotNumber() );
                pstmt.setString( 17, assemblyLot.getCategory() );
                pstmt.setString( 18, assemblyLot.getVendorLotNumber() );
                pstmt.setString( 19, assemblyLot.getDateCode() );

                pstmt.addBatch();
            }

            pstmt.executeBatch();
            success = true;
        }

        catch( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not save Assembly Lot Information into database", sqle );
        }

        finally
        {

            // Commit/Rollback here it selef -- if its not in a manual transaction
            if( dbTransaction == null )
            {
                try
                {
                    if( conn != null )
                    {
                        if( success == true )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch( SQLException ignore )
                {
                }
            }

            if( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch( SQLException ignore )
                {
                }
            }
            if( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch( SQLException ignore )
                {
                }
            }
        }
    }

     /**
     * This method is mainly closes the running lots explicitly.Reason for writing this
     * method suppose today if I get the empty list from the Adaptor, those which are running
     * state lots are not closing. we need to explicitly close those type of lots.
     *
     * @param dbTransaction
     * @param psType
     * @param vendorName
     * @throws ScalarException
     */

    public static void closeAssemblyLot( DBTransaction dbTransaction, String psType, String vendorName ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        try
        {
            // Get the connection from regular datasource if its not in a manual transaction
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

           if ( vendorName != null )
            {
                pstmt = conn.prepareStatement(SqlQueryMappings.get("adaptor.closeAllWipInventoryLots"));
                pstmt.setString(1, psType);
                pstmt.setString(2, vendorName);
                pstmt.executeUpdate();
                pstmt.close();
                pstmt = null;
            }
            else
            {
                pstmt = conn.prepareStatement(SqlQueryMappings.get("adaptor.closeAllWipInventoryLots"));
                pstmt.setString(1, psType);
                pstmt.setString(2, VENDOR_NAME_SCS);
                pstmt.executeUpdate();
                pstmt.close();
                pstmt = null;

                pstmt = conn.prepareStatement(SqlQueryMappings.get("adaptor.closeAllWipInventoryLots"));
                pstmt.setString(1, psType);
                pstmt.setString(2, VENDOR_NAME_SCK);
                pstmt.executeUpdate();
                pstmt.close();
                pstmt = null;

                pstmt = conn.prepareStatement(SqlQueryMappings.get("adaptor.closeAllWipInventoryLots"));
                pstmt.setString(1, psType);
                pstmt.setString(2, VENDOR_NAME_SCC);
                pstmt.executeUpdate();
                pstmt.close();
                pstmt = null;

                pstmt = conn.prepareStatement(SqlQueryMappings.get("adaptor.closeAllWipInventoryLots"));
                pstmt.setString(1, psType);
                pstmt.setString(2, VENDOR_NAME_SCM);
                pstmt.executeUpdate();
                pstmt.close();
                pstmt = null;

            }

            if ( pstmt != null )
            {
                pstmt.close();
                pstmt = null;
            }
            success = true;
        }
        catch( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not save Assembly Lot Information into database", sqle );
        }

        finally
        {
            // Commit/Rollback here it self -- if its not in a manual transaction
            if( dbTransaction == null )
            {
                try
                {
                    if( conn != null )
                    {
                        if( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch( SQLException ignore )
                {
                    ignore.printStackTrace();
                }
                catch( Exception e )
                {
                    e.printStackTrace();
                }
            }

            if( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch( SQLException ignore )
                {
                    ignore.printStackTrace();
                }
            }

            if( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch( SQLException ignore )
                {
                    ignore.printStackTrace();
                }
            }
        }
    }

    public static void saveAssemblyTransactionInQty( DBTransaction dbTransaction, Iterator assemblyLotIterator)  throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        boolean success         = false;

        try
        {
            // Get the connection from regular datasource if its not in a manual transaction
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( " { CALL SP_CREATE_UPDATE_ASSEMBLY_TRANSACTION_QTY ( ?, ?, ?, ?, ?, ?, ? ) }" );
            AssemblyLot assemblyLot = null;
            log( "Saving Assembly Lot Transaction In Qty Information... " );
            while( assemblyLotIterator.hasNext() )
            {
                assemblyLot = ( AssemblyLot ) assemblyLotIterator.next();

                if (assemblyLot.getLotNumber() == null) continue;

                log( assemblyLot + "\n" );

                pstmt.setString( 1, assemblyLot.getDeviceName() );
                pstmt.setString( 2, assemblyLot.getLotNumber() );
                pstmt.setString( 3, assemblyLot.getDateIn() );
                pstmt.setString( 4, assemblyLot.getQtyIn() );
                pstmt.setString( 5, assemblyLot.getVendorName() );
                pstmt.setString( 6, assemblyLot.getFabLotNumber() );
                pstmt.setString( 7, assemblyLot.getDateCode() );

                pstmt.addBatch();
            }

            pstmt.executeBatch();
            success = true;
        }
        catch( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not save Assembly Lot Transaction In qty Information into database", sqle );
        }

        finally
        {

            // Commit/Rollback here it selef -- if its not in a manual transaction
            if( dbTransaction == null )
            {
                try
                {
                    if( conn != null )
                    {
                        if( success == true )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch( SQLException ignore )
                {
                }
            }

            if( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch( SQLException ignore )
                {
                }
            }
            if( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch( SQLException ignore )
                {
                }
            }
        }

    }
    private static void log( String message )
    {
        if( message == null ) return;

        if( s_logger != null )
        {
            synchronized( s_logger )
            {
                s_logger.logDebug( message + LINE_SEPARATOR );
            }
        }
        else
        {
            System.out.println( message );
        }
    }

    public static void setLogger( Logger logger )
    {
        s_logger = logger;
    }

}